﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_CIB003:DA_Matrix
    {
        public DA_CIB003()
        {
            tableName = "企金";
        }

        public DataTable GetDetailData(string p_lancid, string p_yyyymm, string p_accountno,string p_money,string p_sign)
        {
            //ResetCriteria();
            string sql = string.Empty;
            if (string.Empty != p_accountno)
            {
                GetCriteriaString();
                sql = string.Format(getDetailStatement(), getSign(p_sign),CriteriaString
                    ,Utility.GetCIBDetailField("A."));

                FillFieldList(new string[] { p_yyyymm }, new string[] { "@YYYYMM" }, "YYYYMM");
                FillFieldList(new string[] { p_lancid }, new string[] { "@LNAC_ID" }, "LNAC_ID");
                FillFieldList(new string[] { p_accountno }, new string[] { "@ACCOUNTNO" }, "帳號");
                FillFieldList(new string[] { p_money }, new string[] { "@MONEY" }, "均額");

            }
            else
            {
                string[] monCollect = p_yyyymm.Split(new char[] { '_' });
                //ResetCriteria();
                AddCriteria("YYYYMM", "IN", monCollect);
                GetCriteriaString();
                FillFieldList(new string[] { p_lancid }, new string[] { "@LNAC_ID" }, "LNAC_ID");
                FillFieldList(new string[] { p_money }, new string[] { "@MONEY" }, "均額");
                sql = string.Format(getDetailStatementWithNoAccountNo(), CriteriaString
                        , getSign(p_sign), Utility.GetCIBDetailField("A."));

            }
            AddParameter(sql);

            return getData();
        }

        public DataTable GetSummaryData(bool p_checkAccount, string p_money, bool p_rmGroup)
        {
            string sql, checkAccount,tblRM,tblFld,onStr,strNotfound;
            tblRM = p_rmGroup == false ? string.Empty : " LEFT OUTER JOIN RM C ON A.LNAC_ID = C.LNAC_ID";
            tblFld = p_rmGroup == false ? string.Empty : ",RM_GROUP";
            onStr = p_rmGroup == false ? string.Empty : " AND A.RM_GROUP = B.RM_GROUP";
            strNotfound = p_rmGroup == false ? string.Empty : ",ISNULL(RM_GROUP,'NOT FOUND') RM_GROUP";
            GetCriteriaString();
            checkAccount = GetExclusiveOfAccount(p_checkAccount);
            sql = string.Format(getSummaryStatement()
                    , CriteriaString
                    , checkAccount
                    , thanMoreMoney(p_money)
                    , p_money
                    , tblRM
                    , tblFld
                    , onStr
                    , strNotfound
                );
            AddParameter(sql);
            return getData(); 
        }

        /// <summary>
        /// 單一ROW 中會有兩個月份的資料
        /// </summary>
        /// <param name="p_fieldAS">決定新的欄位名</param>
        /// <returns></returns>
        public DataTable GetSummaryData2(bool p_checkAccount,string p_money,
                                            string[] p_months,string[] p_fieldAS
                                        ,string p_sign,bool p_rmGroup)
        {
            string sql, checkAccount,strGroupBy,strGroupBy2,tblRM,tblFld
                ,strNotfound;
            tblRM = p_rmGroup==false ? string.Empty:" LEFT OUTER JOIN RM C ON A.LNAC_ID = C.LNAC_ID" ;
            tblFld = p_rmGroup == false ? string.Empty : "+ '_' + RM_GROUP";
            strNotfound = p_rmGroup == false ? string.Empty : ",ISNULL(C.RM_GROUP,'NOT FOUND') RM_GROUP";
            strGroupBy = string.Empty;
            strGroupBy2 = @"GROUP BY CUBE(A.lnac_id)
		ORDER BY GROUPING(A.LNAC_ID) DESC		";
            sql = string.Empty;
            GetCriteriaString();
            checkAccount = GetExclusiveOfAccount(p_checkAccount);
            if (p_fieldAS.Length.Equals(2))
            {
                CriteriaString = CriteriaString.Replace("AND YYYYMM", "AND A.YYYYMM");
                sql = string.Format(getSummaryStatement2()
                        , CriteriaString
                        , checkAccount
                        , thanMoreMoney(p_money)
                        , getFieldStatement2(p_months, p_fieldAS)
                        , getFieldStatment(p_money, p_fieldAS)
                        , strGroupBy
                        , strGroupBy2
                        , getSign(p_sign)
                        , tblRM
                        , tblFld
                        , strNotfound
                    );
            }
            else
            {
                strGroupBy = "GROUP BY  A.LNAC_ID + '_' + A.LNAC_NAME   ";
                strGroupBy2 = string.Empty;
                string[] fldAS =getFieldStatement3(p_fieldAS);
                sql = string.Format(getSummaryStatement2()
                        , CriteriaString
                        , checkAccount
                        , thanMoreMoney(p_money)
                        , fldAS[0]
                        , fldAS[1]
                        , strGroupBy
                        , strGroupBy2
                        , getSign(p_sign)
                        , tblRM
                        , tblFld
                    );
            }
            AddParameter(sql);
            return getData(); 
        }

        /// <summary>
        /// By客群每月彙總趨勢表
        /// </summary>
        /// <param name="p_checkAccount"></param>
        /// <param name="p_money"></param>
        /// <param name="p_months"></param>
        /// <param name="p_sign"></param>
        /// <param name="p_rmGroup"></param>
        /// <returns></returns>
        public DataTable GetSummaryData3(bool p_checkAccount, string p_money,
                                            string[] p_months , string p_sign
            , bool p_rmGroup,string[] p_rmList)
        {
            /*
             * {0}→RM：LEFT OUTER JOIN RM C ON A.LNAC_ID = C.LNAC_ID
             * {1}AND RM_GROUP IN ('新興金融') 
             * {2}AND A.YYYYMM IN ('201206','201205','201204','201203','201202','201201')
             * {3}AND NOT 帳號 LIKE '411%' 
             * {4}""201206"",""201205"",""201204"",""201203"",""201202"",""201201""
             * {5}加減碼→getSign
             * {6} → 金額大於多少 
             */
            int pos = 1;
            string sql, checkAccount, tblRM,var4,strNotfound,totalSQL;
            sql = "";
            var4 = Utility.GetDoubleQuoteString(p_months);
            strNotfound = p_rmGroup == false ? string.Empty : ",ISNULL(C.RM_GROUP,'NOT FOUND') RM_GROUP";
            tblRM = p_rmGroup == false ? string.Empty : " LEFT OUTER JOIN RM C ON A.LNAC_ID = C.LNAC_ID";
            checkAccount = GetExclusiveOfAccount(p_checkAccount);
            GetCriteriaString();
            StringBuilder sb = new StringBuilder();
            StringBuilder rmBuilder = new StringBuilder();
            if (p_rmGroup.Equals(true))
            {
                foreach (string rm in p_rmList)
                {
                    rmBuilder.AppendFormat(",'{0}'", rm);
                    sql = string.Format(getSummaryStatement3(), tblRM
                        , CriteriaString
                        , string.Format("AND RM_GROUP ='{0}'", rm)
                        , checkAccount
                        , var4
                        , getSign(p_sign)
                        , thanMoreMoney(p_money)
                        , strNotfound
                        , string.Format("'{0}' RM ,p.*",rm)
                        );
                    sb.AppendFormat("# {0}", sql);
                }

                totalSQL = string.Format(getSummaryStatement3(), tblRM
                        , CriteriaString
                        , string.Format("AND RM_GROUP in ({0})", rmBuilder.ToString().Substring(1))
                        , checkAccount
                        , var4
                        , getSign(p_sign)
                        , thanMoreMoney(p_money)
                        , strNotfound
                        , string.Format("'{0}' RM ,p.*", "TOTAL")
                        ); 
                sql = "SELECT * FROM (" + 
                    sb.ToString().Substring(1).Replace("#", " UNION ALL") + 
                    " UNION ALL " + totalSQL +
                    @") A ORDER BY 
	CASE 
        WHEN RM = '企業金融' THEN 1		
        WHEN RM = '商業金融' THEN 2        
        WHEN RM = '海外業務' THEN 3
        WHEN RM = '新興金融' THEN 4
		WHEN RM = '公庫' THEN 6
		WHEN RM = 'NOT FOUND' THEN 7
		WHEN RM = 'TOTAL' THEN 8		
	END,
	CASE 	
		WHEN TYP = 'AVG_承作利率' THEN 1
		WHEN TYP = 'AVG_FTP_RATE' THEN 2
		WHEN TYP = '利差' THEN 3
		WHEN TYP = 'AVG_加減碼' THEN 4
		ELSE 5 END

";
            }
            else
            {
                sql = string.Format(getSummaryStatement3(), tblRM
                    , CriteriaString
                    , ""
                    , checkAccount
                    , var4
                    , getSign(p_sign)
                    , thanMoreMoney(p_money)
                    , strNotfound
                    , "*"
                    ) + @"
ORDER BY CASE 	
		WHEN TYP = 'AVG_承作利率' THEN 1
		WHEN TYP = 'AVG_FTP_RATE' THEN 2
		WHEN TYP = '利差' THEN 3
		WHEN TYP = 'AVG_加減碼' THEN 4
		ELSE 5 END
";            
            }
            AddParameter(sql);
            DataTable tmpDT = getData();
            DataTable newDT = new DataTable();
            for (int i = 0; i < tmpDT.Columns.Count; i++)
            {
                newDT.Columns.Add(tmpDT.Columns[i].ColumnName);    
                if (tmpDT.Columns[i].ColumnName.Equals("RM"))
                {
                    pos = 2;
                }
            }
            
            foreach (DataRow row in tmpDT.Rows)
            {
                DataRow newRow = newDT.NewRow();
                for (int i = 0; i < tmpDT.Columns.Count; i++)
                {
                    newRow[i] = i < pos ? row[i].ToString()
                        : string.Format("{0}{1}", row[i].ToString()
                                        , row[pos-1].ToString().Equals("部位(億元)") ? "" : "%")
                    ;
                }
                newDT.Rows.Add(newRow);                
            }
            
            return newDT;
        }

        public override void ResetCriteria()
        {
            base.ResetCriteria();
            tableSchema.Add("RM_GROUP", DbType.String);
        }

        protected string getSign(string p_sign)
        {
            string strSign = string.Empty;
            switch(p_sign)
            {
                case "0":
                    strSign = " AND 1=1 ";
                    break;
                case "1":
                    strSign = " AND 加減碼 > 0 ";
                    break;
                case "2":
                    strSign = " AND 加減碼 <= 0 ";
                    break;
            }

            return strSign;
        }

        protected string thanMoreMoney(string p_money)
        {
            return string.Format("AND A.均額 > {0}"
                , (p_money.Length == 0) ? "0":p_money );
        }

        protected string getSummaryStatement()
        {
            /*
            {0} → WHERE
            {1} → 排除公庫
            {2} → 金額大於多少 
            {3} → 當金額以多少為單位時，相對的均額也要除EX:以億為單位時，均額也要用億來計價
            {4} →  LEFT OUTER JOIN RM C ON A.LNAC_ID = C.LNAC_ID
            {5} → RM_GROUP
            {6} → ON 
            {7} → RM_GROUP 加入 NOT FOUND
             */
            return @"
                    SELECT 
                        dbo.ReplaceCIBValue([LNAC_NAME],1)LNAC_NAME
                        ,A.YYYYMM
                        ,dbo.ReplaceCIBValue(A.帳號,2)帳號
                        ,A.FTP_RATE
                        ,A.承作利率
                        ,A.CUR
                        ,B.CNT
                    FROM(
                            SELECT 
                                A.LNAC_ID
                                ,A.LNAC_NAME
                                ,A.YYYYMM
                                ,A.帳號
                                ,A.FTP_RATE
                                ,A.承作利率
                                ,A.CUR
                                {7}
                                ,CONVERT(NVARCHAR,CAST(A.均額/{3} AS MONEY),1) 均額
                            FROM dbo.企金 A {4}
                            WHERE 1=1  
                            {0}
                             {1}
                             {2}                            
                        )A LEFT OUTER JOIN (
                                            SELECT
                                                    A.LNAC_ID
                                                    ,A.帳號
                                                    ,COUNT(*) CNT
                                                    {7}
	                                        FROM dbo.企金 A {4}
	                                        WHERE 1=1  
                                                {0}
                                                 {1}
                                                 {2}
	                                        GROUP BY A.LNAC_ID,A.帳號 {5})B
                        ON A.帳號=B.帳號	{6}
                        ORDER BY A.LNAC_ID,A.帳號,A.YYYYMM
";
        }

        protected string getSummaryStatement2()
        {
            /*
                {0} → WHERE
                {1} → 排除公庫
                {2} → 金額大於多少 
                {3} → 當金額以多少為單位時，相對的均額也要除EX:以億為單位時，均額也要用億來計價
                {4} → 依所選的月份來做ROW TO COL 
                {7} → 加減碼
                {8} → RM TABLE lEFT OUTER  JOIN RM C ON A.LNAC_ID = C.LNAC_ID
                {9} → 把RM_GROUP 加入到 LNAC_ID 中
                {10}→ RM_GROUP NOT FOUND 
            */

            return @"SELECT
		ISNULL(A.lnac_id,'合計')  AS LNAC_ID
		{4}						
	FROM(		
			SELECT 
				 dbo.myEncryptDecryptString(A.LNAC_ID) + '_' + ISNULL(dbo.ReplaceCIBValue(A.LNAC_NAME,1),'')  {9} as lnac_id
				 {3}
				 
			 FROM(
		            SELECT 
			            A.LNAC_ID,A.LNAC_NAME,A.FTP_RATE ,A.承作利率 ,A.加減碼
			            ,A.均額,A.YYYYMM,A.CUR,A.帳號,A.會計科目
                        {10}
                    FROM dbo.企金 A
                    {8}
                )
                AS  A LEFT OUTER JOIN SUBSIDY B
	            ON a.帳號 = B.放款帳號 AND A.YYYYMM = B.YYYYMM                
			 WHERE 1=1  
				    {0}
                    {1}
                    {2}
                    {7}
                    {5}   

		)A  {6} ";

        }

        protected string getSummaryStatement3()
        {
            /*
             * {0}→RM：LEFT OUTER JOIN RM C ON A.LNAC_ID = C.LNAC_ID
             * {1}AND RM_GROUP IN ('新興金融') 
             * {2}AND A.YYYYMM IN ('201206','201205','201204','201203','201202','201201')
             * {3}AND NOT 帳號 LIKE '411%' 
             * {4}""201206"",""201205"",""201204"",""201203"",""201202"",""201201""
             * {5}加減碼→getSign
             * {6} → 金額大於多少 
             * {7} → RM_GROUP NOT FOUND 
             * {8} → * 或是某一個RM_GROUP
             */
            return @"
SELECT 	{8}
FROM(
SELECT 	YYYYMM	,TYP 	,TWD
FROM
(
SELECT
	A.YYYYMM
	,SUM(A.均額 * A.FTP_RATE)/ SUM(A.均額)  AVG_FTP_RATE
	,SUM(A.均額 * A.承作利率)/ SUM(A.均額)  AVG_承作利率
	,SUM(A.均額 * A.加減碼 )/ SUM(A.均額)   AVG_加減碼
	,SUM(A.均額 * A.承作利率)/ SUM(A.均額) - SUM(A.均額 * A.FTP_RATE)/ SUM(A.均額) AS 利差
	,SUM(A.均額)/100000000 ""部位(億元)""
FROM (
        SELECT
		A.均額,A.FTP_RATE,A.承作利率,A.加減碼 ,A.會計科目 
		,A.CUR,A.帳號,A.YYYYMM 
		{7}
        FROM dbo.企金 AS A {0}
)A
WHERE 1=1  
    {1}
	{2}
    {3}
    {5}
    {6}
GROUP BY A.YYYYMM	
)A UNPIVOT(
	TWD FOR TYP IN(""AVG_FTP_RATE"",""AVG_承作利率"",""AVG_加減碼""
					,""利差"",""部位(億元)"")
)P	)A PIVOT(
	SUM(TWD) FOR YYYYMM IN ({4})
)P

";
        }

        protected string getDetailStatement()
        {
            return @"SELECT
	                        {2}
                        FROM 企金 A
                        WHERE A.YYYYMM = @YYYYMM
                        AND A.LNAC_ID = dbo.myEncryptDecryptString(@LNAC_ID)
                        AND A.帳號 = @ACCOUNTNO
                        AND A.均額 > @MONEY
                        {0}
                        {1}
";
        }

        protected string getDetailStatementWithNoAccountNo()
        {
            return @"SELECT
	B.CNT AS [筆數]
	,{2}
FROM (						
						SELECT
	                        *
                        FROM 企金 A
                         WHERE 1=1 {0}
                        AND A.LNAC_ID = dbo.myEncryptDecryptString(@LNAC_ID)
                         {1}
                        AND A.均額 > @MONEY)A 
                       
                        LEFT OUTER JOIN (
                        
SELECT
	COUNT(*) AS CNT
	,A.LNAC_ID,A.帳號                   
FROM 企金 A
WHERE 1=1 {0}
                        AND A.LNAC_ID = dbo.myEncryptDecryptString(@LNAC_ID)
                        
                        AND A.均額 > @MONEY
                        {1}
GROUP BY A.LNAC_ID,A.帳號)B 
ON A.帳號 = B.帳號
";
        }

        protected string[] getInStatement(string[] p_month)
        {
            string[] mon = { string.Empty, string.Empty };
            int i = 0;
            foreach (string str in p_month)
            {
                var parms = str.Split(new char[] { ',' });
                var p2 = parms.Select((s) => string.Format("'{0}'", s)).ToArray();
                mon[i] = string.Join(",", p2);
                i++;
            }
            return mon;
        }

        protected string getFieldStatement2(string[] p_month,string[] p_fieldAS)
        {
            string[] mon = getInStatement(p_month);

            return string.Format(@" 
                 ,CASE WHEN A.YYYYMM IN ({0}) THEN A.均額 ELSE 0 END AS [{2}_均額]
				 ,CASE WHEN A.YYYYMM IN ({1}) THEN A.均額 ELSE 0 END AS [{3}_均額]
				 ,CASE WHEN A.YYYYMM IN ({0}) THEN A.均額 * A.FTP_RATE  ELSE 0 END AS [{2}_FTP_RATE]
				 ,CASE WHEN A.YYYYMM IN ({1}) THEN A.均額 * A.FTP_RATE  ELSE 0 END AS [{3}_FTP_RATE]
				 ,CASE WHEN A.YYYYMM IN ({0}) THEN A.均額 * A.承作利率  ELSE 0 END AS [{2}_承作利率]
				 ,CASE WHEN A.YYYYMM IN ({1}) THEN A.均額 * A.承作利率  ELSE 0 END AS [{3}_承作利率]
				 ,CASE WHEN A.YYYYMM IN ({0}) THEN A.均額 * A.加減碼  ELSE 0 END AS [{2}_加減碼]
				 ,CASE WHEN A.YYYYMM IN ({1}) THEN A.均額 * A.加減碼  ELSE 0 END AS [{3}_加減碼]
		         ,CASE WHEN A.YYYYMM IN ({0}) THEN 	 	
			        ISNULL(A.均額 * Dbo.FtpRateADJ(A.均額,dbo.GetFundingCost(A.均額,A.FTP_RATE,A.YYYYMM,A.CUR),B.當月貼補金額,B.YYYYMM,A.CUR),0)	
			        ELSE 0 END AS [{2}_FtpRateADJ]				
		        ,CASE WHEN A.YYYYMM IN ({1}) THEN 	 	
			        ISNULL(A.均額 * dbo.FtpRateADJ(A.均額,dbo.GetFundingCost(A.均額,A.FTP_RATE,A.YYYYMM,A.CUR),B.當月貼補金額,B.YYYYMM,A.CUR),0)	
			        ELSE 0 END AS [{3}_FtpRateADJ]							

 ", mon[0], mon[1]
  , p_fieldAS[0].ToString()
  , p_fieldAS[1].ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_money"></param>
        /// <param name="p_fields"></param>
        /// <returns></returns>
        protected string getFieldStatment(string p_money,string[] p_fields)
        {
//            return string.Format(@"
//              	,CONVERT(NVARCHAR,CAST(A.[{0}均額]/{2} AS MONEY),1)  [{0}均額]
//            	,CONVERT(NVARCHAR,CAST(A.[{1}均額]/{2} AS MONEY),1)  [{1}均額]
//	            ,CONVERT(NVARCHAR,CAST((A.[{1}均額] - A.[{0}均額] )/{2} AS MONEY),1)AS [均額差異]
//	            ,A.[{0}FTP_RATE]
//	            ,A.[{1}FTP_RATE]
//                                ", p_month[0],p_month[1],p_money ) ;
//            return string.Format(@" 
//,ROUND(SUM(A.[First_均額])/{0},2) AS  [First_均額]
//,ROUND(SUM(A.[Second_均額])/{0},2) AS [Second_均額]
//,ROUND((SUM(A.[Second_均額]) - SUM(A.[First_均額]))/{0},2) AS [均額差異]
//
//,CASE WHEN SUM(A.[First_承作利率]) = 0 THEN 0 
//    ELSE ROUND(SUM(A.[First_承作利率]) / SUM(A.[First_均額])/{0},2) END AS [First_承作利率]        		
//,CASE WHEN SUM(A.[Second_均額]) = 0 THEN 0 	
//    ELSE ROUND(SUM(A.[Second_承作利率]) / SUM(A.[Second_均額])/{0},2) END AS [Second_承作利率]
//
//,ROUND((CASE WHEN SUM(A.[Second_均額]) = 0 THEN 0 	
//    ELSE SUM(A.[Second_承作利率]) / SUM(A.[Second_均額]) END) -
// (CASE WHEN SUM(A.[First_均額]) = 0 THEN 0 
//    ELSE SUM(A.[First_承作利率]) / SUM(A.[First_均額]) END),2)	AS [承作利率差異]
//
//,CASE WHEN SUM(A.[First_均額]) = 0 THEN 0 	
//    ELSE ROUND(SUM(A.[First_FTP_RATE]) / SUM(A.[First_均額]),2) END AS [First__FTP_RATE]	        		
//,CASE WHEN SUM(A.[Second_均額]) = 0 THEN 0 	
//    ELSE ROUND(SUM(A.[Second_FTP_RATE]) / SUM(A.[Second_均額]),2) END AS [Second_FTP_RATE]	
//
//,ROUND((CASE WHEN SUM(A.[Second_均額]) = 0 THEN 0 	
//    ELSE SUM(A.[Second_FTP_RATE]) / SUM(A.[Second_均額]) END) -
// (CASE WHEN SUM(A.[First_均額]) = 0 THEN 0 	
//    ELSE SUM(A.[First_FTP_RATE]) / SUM(A.[First_均額]) END),2) AS [FTP_RATE差異]
//
//,ROUND((CASE WHEN SUM(A.[First_承作利率]) = 0 THEN 0 
//    ELSE SUM(A.[First_承作利率]) / SUM(A.[First_均額]) END) -
// (CASE WHEN SUM(A.[First_均額]) = 0 THEN 0 	
//    ELSE SUM(A.[First_FTP_RATE]) / SUM(A.[First_均額]) END),2) AS [First_利差]
//	
//,ROUND((CASE WHEN SUM(A.[Second_承作利率]) = 0 THEN 0 
//    ELSE SUM(A.[Second_承作利率]) / SUM(A.[Second_均額]) END) -
// (CASE WHEN SUM(A.[Second_均額]) = 0 THEN 0 	
//    ELSE SUM(A.[Second_FTP_RATE]) / SUM(A.[Second_均額]) END),2) AS [Second_利差]",
//                                                                              p_money);
            return string.Format(@",ROUND(SUM(A.[{0}_均額])/{2},2) AS  [{0}_均額]
,ROUND(SUM(A.[{1}_均額])/{2},2) AS [{1}_均額]
,ROUND((SUM(A.[{1}_均額]) - SUM(A.[{0}_均額]))/{2},2) AS [均額差異]

,CASE WHEN SUM(A.[{0}_承作利率]) = 0 THEN 0 
    ELSE ROUND(SUM(A.[{0}_承作利率]) / SUM(A.[{0}_均額]),2) END AS [{0}_承作利率]        		
,CASE WHEN SUM(A.[{1}_均額]) = 0 THEN 0 	
    ELSE ROUND(SUM(A.[{1}_承作利率]) / SUM(A.[{1}_均額]),2) END AS [{1}_承作利率]

,ROUND((CASE WHEN SUM(A.[{1}_均額]) = 0 THEN 0 	
    ELSE SUM(A.[{1}_承作利率]) / SUM(A.[{1}_均額]) END) -
 (CASE WHEN SUM(A.[{0}_均額]) = 0 THEN 0 
    ELSE SUM(A.[{0}_承作利率]) / SUM(A.[{0}_均額]) END),2)	AS [承作利率差異]

,CASE WHEN SUM(A.[{0}_均額]) = 0 THEN 0 	
    ELSE ROUND(SUM(A.[{0}_FTP_RATE]) / SUM(A.[{0}_均額]),2) END AS [{0}_FTP_RATE]	        		
,CASE WHEN SUM(A.[{1}_均額]) = 0 THEN 0 	
    ELSE ROUND(SUM(A.[{1}_FTP_RATE]) / SUM(A.[{1}_均額]),2) END AS [{1}_FTP_RATE]	

,ROUND((CASE WHEN SUM(A.[{1}_均額]) = 0 THEN 0 	
    ELSE SUM(A.[{1}_FTP_RATE]) / SUM(A.[{1}_均額]) END) -
 (CASE WHEN SUM(A.[{0}_均額]) = 0 THEN 0 	
    ELSE SUM(A.[{0}_FTP_RATE]) / SUM(A.[{0}_均額]) END),2) AS [FTP_RATE差異]

,ROUND((CASE WHEN SUM(A.[{0}_承作利率]) = 0 THEN 0 
    ELSE SUM(A.[{0}_承作利率]) / SUM(A.[{0}_均額]) END) -
 (CASE WHEN SUM(A.[{0}_均額]) = 0 THEN 0 	
    ELSE SUM(A.[{0}_FTP_RATE]) / SUM(A.[{0}_均額]) END),2) AS [{0}_利差]
	
,ROUND((CASE WHEN SUM(A.[{1}_承作利率]) = 0 THEN 0 
    ELSE SUM(A.[{1}_承作利率]) / SUM(A.[{1}_均額]) END) -
 (CASE WHEN SUM(A.[{1}_均額]) = 0 THEN 0 	
    ELSE SUM(A.[{1}_FTP_RATE]) / SUM(A.[{1}_均額]) END),2) AS [{1}_利差]

,ROUND(((CASE WHEN SUM(A.[{1}_承作利率]) = 0 THEN 0 
    ELSE SUM(A.[{1}_承作利率]) / SUM(A.[{1}_均額]) END) -
 (CASE WHEN SUM(A.[{1}_均額]) = 0 THEN 0 	
    ELSE SUM(A.[{1}_FTP_RATE]) / SUM(A.[{1}_均額]) END)) -			
((CASE WHEN SUM(A.[{0}_承作利率]) = 0 THEN 0 
    ELSE SUM(A.[{0}_承作利率]) / SUM(A.[{0}_均額]) END) -
 (CASE WHEN SUM(A.[{0}_均額]) = 0 THEN 0 	
    ELSE SUM(A.[{0}_FTP_RATE]) / SUM(A.[{0}_均額]) END)),2) AS 利差差異	

,CASE WHEN SUM(A.[{0}_均額]) = 0 THEN 0 	
		ELSE ROUND(SUM(A.[{0}_FtpRateADJ]) / SUM(A.[{0}_均額]),2) END AS [{0}_FtpRateADJ]
,CASE WHEN SUM(A.[{1}_均額]) = 0 THEN 0 	
		ELSE ROUND(SUM(A.[{1}_FtpRateADJ]) / SUM(A.[{1}_均額]),2) END AS [{1}_FtpRateADJ]
	
,ROUND((CASE WHEN SUM(A.[{1}_均額]) = 0 THEN 0 	
	ELSE SUM(A.[{1}_FtpRateADJ]) / SUM(A.[{1}_均額]) END) -
    (CASE WHEN SUM(A.[{0}_均額]) = 0 THEN 0 	
	ELSE SUM(A.[{0}_FtpRateADJ]) / SUM(A.[{0}_均額]) END),2) AS [FtpRateADJ差異]

,CASE WHEN SUM(A.[{0}_加減碼]) = 0 THEN 0 
    ELSE ROUND(SUM(A.[{0}_加減碼]) / SUM(A.[{0}_均額]),2) END AS [{0}_加減碼]        		
,CASE WHEN SUM(A.[{1}_均額]) = 0 THEN 0 	
	ELSE ROUND(SUM(A.[{1}_加減碼]) / SUM(A.[{1}_均額]),2) END AS [{1}_加減碼]

,ROUND((CASE WHEN SUM(A.[{1}_均額]) = 0 THEN 0 	
	ELSE ROUND(SUM(A.[{1}_加減碼]) / SUM(A.[{1}_均額]),2) END )
		-(CASE WHEN SUM(A.[{0}_加減碼]) = 0 THEN 0 
	ELSE ROUND(SUM(A.[{0}_加減碼]) / SUM(A.[{0}_均額]),2) END),2) [加減碼差異]
",
                                  p_fields[0].ToString(),p_fields[1].ToString(),p_money);

        }


        /// <summary>
        /// 給季來使用，傳入一個陣列
        /// </summary>
        /// <param name="p_fields"></param>
        /// <returns></returns>
        protected string[] getFieldStatement3(string[] p_fields)
        {
            /*
             * {0} 最小金額EX:1億             
			 * {1}{3}{5} 第一個季中的三個月份 
			 * {2}{4}{6} 第一個季中月份的實際天數
			 * {7} 第一個季中月份的總天數             
			 * {8}{10}{12} 第二個季中的三個月份
             * {9}{11}{13} 第二個季中月份的實際天數			              
			 * {14} 第二個季中的總天數			 
             * {15}      EX:YYYYQ1 
             * {16}      EX:YYYYQ2
             */
            string fld1, fld2;
            List<string> fieldsCollect = new List<string>();
            fieldsCollect.Add(string.Empty);
            for (int i = 1; i < p_fields.Length; i++)
            {
                fieldsCollect.Add(p_fields[i].ToString());
            }

            #region fld1
            fld1 = string.Format(@"{0}
			            ,SUM(CASE 
				            WHEN A.YYYYMM = '{1}' THEN A.均額 * {2}   	
				            WHEN A.YYYYMM = '{3}' THEN A.均額 * {4}   	
				            WHEN A.YYYYMM = '{5}' THEN A.均額 * {6}
				            ELSE 0 END)/{7} [{15}均額]				
			             ,SUM(CASE 
				            WHEN A.YYYYMM = '{8}'  THEN A.均額 * {9}   	
				            WHEN A.YYYYMM = '{10}' THEN A.均額 * {11}   	
				            WHEN A.YYYYMM = '{11}' THEN A.均額 * {12}
				            ELSE 0 END)/{14} [{16}均額]   				
			            ,CASE 
				            WHEN 
					            SUM(CASE 
						            WHEN A.YYYYMM = '{1}' THEN A.均額
						            WHEN A.YYYYMM = '{3}' THEN A.均額
						            WHEN A.YYYYMM = '{5}' THEN A.均額
						            ELSE 0 END) = 0 THEN 0 
				            ELSE
					            SUM(CASE
						            WHEN A.YYYYMM = '{1}' THEN A.均額 * A.承作利率   	
						            WHEN A.YYYYMM = '{3}' THEN A.均額 * A.承作利率
						            WHEN A.YYYYMM = '{5}' THEN A.均額 * A.承作利率 
						            ELSE 0 END) /
					            SUM(CASE 
						            WHEN A.YYYYMM = '{1}' THEN A.均額
						            WHEN A.YYYYMM = '{3}' THEN A.均額
						            WHEN A.YYYYMM = '{5}' THEN A.均額
						            ELSE 0 END) 
				            END AS [{15}承作利率]
			            ,CASE 
				            WHEN 
					            SUM(CASE 
						            WHEN A.YYYYMM = '{8}' THEN A.均額
						            WHEN A.YYYYMM = '{10}' THEN A.均額
						            WHEN A.YYYYMM = '{11}' THEN A.均額
						            ELSE 0 END) = 0 THEN 0 
				            ELSE
					            SUM(CASE
						            WHEN A.YYYYMM = '{8}' THEN A.均額 * A.承作利率   	
						            WHEN A.YYYYMM = '{10}' THEN A.均額 * A.承作利率
						            WHEN A.YYYYMM = '{11}' THEN A.均額 * A.承作利率 
						            ELSE 0 END) /
					            SUM(CASE 
						            WHEN A.YYYYMM = '{8}' THEN A.均額
						            WHEN A.YYYYMM = '{10}' THEN A.均額
						            WHEN A.YYYYMM = '{11}' THEN A.均額
						            ELSE 0 END) 
				            END AS [{16}承作利率]
			            ,CASE 
				            WHEN 
					            SUM(CASE 
						            WHEN A.YYYYMM = '{1}' THEN A.均額
						            WHEN A.YYYYMM = '{3}' THEN A.均額
						            WHEN A.YYYYMM = '{5}' THEN A.均額
						            ELSE 0 END) = 0 THEN 0 
				            ELSE
					            SUM(CASE
						            WHEN A.YYYYMM = '{1}' THEN A.均額 * A.FTP_RATE
						            WHEN A.YYYYMM = '{3}' THEN A.均額 * A.FTP_RATE
						            WHEN A.YYYYMM = '{5}' THEN A.均額 * A.FTP_RATE
						            ELSE 0 END) /
					            SUM(CASE 
						            WHEN A.YYYYMM = '{1}' THEN A.均額
						            WHEN A.YYYYMM = '{3}' THEN A.均額
						            WHEN A.YYYYMM = '{5}' THEN A.均額
						            ELSE 0 END) 
				            END AS [{15}FTP_RATE]						
			            ,CASE 
				            WHEN 
					            SUM(CASE 
						            WHEN A.YYYYMM = '{8}' THEN A.均額
						            WHEN A.YYYYMM = '{10}' THEN A.均額
						            WHEN A.YYYYMM = '{11}' THEN A.均額
						            ELSE 0 END) = 0 THEN 0 
				            ELSE
					            SUM(CASE
						            WHEN A.YYYYMM = '{8}' THEN A.均額 * A.FTP_RATE
						            WHEN A.YYYYMM = '{10}' THEN A.均額 * A.FTP_RATE
						            WHEN A.YYYYMM = '{11}' THEN A.均額 * A.FTP_RATE
						            ELSE 0 END) /
					            SUM(CASE 
						            WHEN A.YYYYMM = '{8}' THEN A.均額
						            WHEN A.YYYYMM = '{10}' THEN A.均額
						            WHEN A.YYYYMM = '{11}' THEN A.均額
						            ELSE 0 END) 
				            END AS [{16}FTP_RATE]
            ", fieldsCollect.ToArray());
            #endregion
            #region
            fld2 = string.Format(@",ROUND(A.[{1}均額]/{0},2) AS [{1}均額]
	,ROUND(A.[{2}均額]/{0},2) AS [{2}均額]
	,ROUND((A.[{2}均額]/{0})-(A.[{1}均額]/{0}),2)  AS 均額差異
	,ROUND(A.[{1}承作利率],2) AS [{1}承作利率]
	,ROUND(A.[{2}承作利率],2) AS [{2}承作利率]
	,ROUND(A.[{2}承作利率] - A.[{1}承作利率],2) AS 承作利率差異
	,ROUND(A.[{1}FTP_RATE],2) AS [{1}FTP_RATE]
	,ROUND(A.[{2}FTP_RATE],2) AS [{2}FTP_RATE]
	,ROUND(A.[{2}FTP_RATE] - A.[{1}FTP_RATE],2) AS FTP_RATE差異
	,ROUND(A.[{1}承作利率] - A.[{1}FTP_RATE],2) AS [{1}利差]
	,ROUND(A.[{2}承作利率] - A.[{2}FTP_RATE],2) AS [{2}利差]
", p_fields[0],p_fields[15],p_fields[16]);
            #endregion                        

            return new string[]{fld1,fld2};
        }

        public override string[] GetDistinctField()
        {
            return new string[]{
                             "YYYYMM"
                            ,"CUR"
                            ,"會計科目"   
                            ,"利率調整型態"
                            };
        }

        public override DataSet GetCriteria()
        {
            //DataSet ds = base.GetCriteria();
            DataSet ds = new DataSet();
            ds.Tables.Add(Utility.GetYYYYMMRange(Convert.ToDateTime("2009-09-01")));
            ds.Tables.Add(Utility.GetCIBCurData());
            ds.Tables.Add(Utility.GetCIBAccountNoData());
            ds.Tables.Add(Utility.GetCIBRateAdjType());
            DataTable dt, dt1, dt2, dtYYYY2;
            dt = ds.Tables["YYYYMM"];
            dt1 = dt.Copy(); //dt.Clone();            
            dt1.TableName = "YYYYMM1";
            dt2 = dt.Copy(); //dt.Clone();
            dt2.TableName = "YYYYMM2";
            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);

            var distinctYM = (from DataRow row in dt.Rows
                              select ((string)row["YYYYMM"]).Substring(0, 4)).Distinct();


            DataTable dtYYYY = new DataTable();
            dtYYYY.Columns.Add("YYYY", typeof(System.String));

            foreach (var str in distinctYM)
            {
                DataRow row = dtYYYY.NewRow();
                row[0] = str;
                dtYYYY.Rows.Add(row);
            }
            dtYYYY.TableName = "Season1";
            dtYYYY2 = dtYYYY.Copy();
            dtYYYY2.TableName = "Season2";

            ds.Tables.Add(dtYYYY);
            ds.Tables.Add(dtYYYY2);

            DataTable dtRMGroup = Utility.GetParameterByLevel("CIB", "RM_GROUP");
            DataTable dtRm = new DataTable("RM_GROUP");
            dtRm.Columns.Add("RM_GROUP", typeof(System.String));
            foreach (DataRow rmRow in dtRMGroup.Rows)
            {
                DataRow row1 = dtRm.NewRow();
                row1[0] = rmRow["fldvalue"].ToString();
                dtRm.Rows.Add(row1);
            }

            ds.Tables.Add(dtRm);

            return ds;
        }

       
    }
}
